package com.ssm.util;
import java.io.File;  
import java.io.FileInputStream;  
import java.io.FileNotFoundException;  
import java.io.IOException;  
import java.text.DecimalFormat;  
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedList;  
import java.util.List;  
  
import org.apache.poi.hssf.usermodel.HSSFCell;  
import org.apache.poi.hssf.usermodel.HSSFDateUtil;  
import org.apache.poi.hssf.usermodel.HSSFRow;  
import org.apache.poi.hssf.usermodel.HSSFSheet;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.apache.poi.xssf.usermodel.XSSFCell;  
import org.apache.poi.xssf.usermodel.XSSFRow;  
import org.apache.poi.xssf.usermodel.XSSFSheet;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
/** 
 * 读取excel文件 
 * @author Jiacheng 
 *Poi解析2003时使用的是HSSFCell，而2007的则是  
    XSSFCell，是完全不同的两套API 
    必须先要判断excel的类型，不过 HSSFWorkbook 和 XSSFWorkbook 实现的接口都是一样的Workbook，直接在实例化接口的时候有点区别其他时候没有任何差异。 
 */
public class ImportExecl {  
	
    public static List<List<Object>> readExcel(File file) throws IOException {  
        String fileName = file.getName();  
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName  
                .substring(fileName.lastIndexOf(".") + 1);  
        if ("xls".equals(extension)) {  
            return read2003Excel(file);  
        } else if ("xlsx".equals(extension)) {  
            return read2007Excel(file);  
        } else {  
            throw new IOException("不支持的文件类型");  
        }  
    }  
  
    /** 
     * 读取 office 2003 excel 
     *  
     * @throws IOException 
     * @throws FileNotFoundException 
     */  
    private static List<List<Object>> read2003Excel(File file)  
            throws IOException {  
        List<List<Object>> list = new LinkedList<List<Object>>();  
        HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));  
        HSSFSheet sheet = hwb.getSheetAt(0);  
        Object value = null;  
        HSSFRow row = null;  
        HSSFCell cell = null;  
        int counter = 0;  
        for (int i = sheet.getFirstRowNum(); counter < sheet  
                .getPhysicalNumberOfRows(); i++) {  
//          if(i==0){  
//              //跳过第一行  
//              continue;  
//          }  
            row = sheet.getRow(i);  
            List<Object> linked = new LinkedList<Object>();  
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {  
                cell = row.getCell(j);  
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String  
                                                            // 字符  
                SimpleDateFormat sdf = new SimpleDateFormat(  
                        "yyyy-MM-dd  hh:mm");// 格式化日期字符串  
                DecimalFormat nf = new DecimalFormat("0");// 格式化数字  
                switch (cell.getCellType()) {  
                case XSSFCell.CELL_TYPE_STRING:  
                    value = cell.getStringCellValue();  
                    //System.out.println(i + "行" + j + " 列 is String type" +"  "+value);  
                    break;  
                case XSSFCell.CELL_TYPE_NUMERIC:  
//                  System.out.println(i + "行" + j  
//                          + " 列 is Number type ; DateFormt:"  
//                          + cell.getCellStyle().getDataFormatString());  
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {  
                        value = df.format(cell.getNumericCellValue());  
                    } else if ("General".equals(cell.getCellStyle()  
                            .getDataFormatString())) {  
                        value = nf.format(cell.getNumericCellValue());  
                    } else {  
                        value = sdf.format(HSSFDateUtil.getJavaDate(cell  
                                .getNumericCellValue()));  
                    }  
                    break;  
                case XSSFCell.CELL_TYPE_BOOLEAN:  
                    value = cell.getBooleanCellValue();  
                    break;  
                case XSSFCell.CELL_TYPE_BLANK:  
                    value = "";  
                    break;  
                default:  
                    value = cell.toString();  
                }  
                //System.out.println(value);  
                linked.add(value);  
            }  
            list.add(linked);  
        }  
        return list;  
    }  
  
    /** 
     * 读取Office 2007 excel 
     * */  
    private static List<List<Object>> read2007Excel(File file)  
            throws IOException {  
        List<List<Object>> list = new LinkedList<List<Object>>();  
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径  
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));  
        // 读取第一章表格内容  
        XSSFSheet sheet = xwb.getSheetAt(0);  
        Object value = null;  
        XSSFRow row = null;  
        XSSFCell cell = null;  
        int counter = 0;  
        for (int i = sheet.getFirstRowNum(); counter < sheet  
                .getPhysicalNumberOfRows(); i++) {  
            if(i==0){  
                //跳过第一行  
                continue;  
            }  
            row = sheet.getRow(i);  
            if (row == null) {  
                 break;  
            }  
            List<Object> linked = new LinkedList<Object>();  
            for (int j = row.getFirstCellNum(); j <row.getLastCellNum(); j++) {  
                cell = row.getCell(j);  
                //System.out.println(value);  
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String  
                                                            // 字符  
                SimpleDateFormat sdf = new SimpleDateFormat(  
                        "yyyy-MM-dd hh:mm");// 格式化日期字符串  
                DecimalFormat nf = new DecimalFormat("0");// 格式化数字  
                switch (cell.getCellType()) {  
                        case XSSFCell.CELL_TYPE_STRING:  
                            //System.out.println(i + "行" + j + " 列 is String type");  
                            value = cell.getStringCellValue();  
                            break;  
                        case XSSFCell.CELL_TYPE_NUMERIC:  
                        //  System.out.println(i + "行" + j  
                                //  + " 列 is Number type ; DateFormt:"  
                                //  + cell.getCellStyle().getDataFormatString());  
                            if ("@".equals(cell.getCellStyle().getDataFormatString())) {  
                                value = df.format(cell.getNumericCellValue());  
                            } else if ("General".equals(cell.getCellStyle()  
                                    .getDataFormatString())) {  
                                value = nf.format(cell.getNumericCellValue());  
                            } else {  
                                value = sdf.format(HSSFDateUtil.getJavaDate(cell  
                                        .getNumericCellValue()));  
                            }  
                            break;  
                        case XSSFCell.CELL_TYPE_BOOLEAN:  
                            value = cell.getBooleanCellValue();  
                            break;  
                        case XSSFCell.CELL_TYPE_BLANK://空格，空白  
                            value = "";  
                            break;  
                        default:  
                            value = cell.toString();  
                }  
                //System.out.println(value);  
                linked.add(value);  
            }  
            list.add(linked);  
        }  
        return list;  
    }  
}  
